package com.edgar.sqlite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.UUID;

/**
 * @author liqs
 * @version 1.0
 * @date 2021/11/2 15:19
 */
public class SqliteCient {

    /**
     * 这是个非常简单的SQLite的Java程序，
     * 程序中创建数据库、创建表、然后插入数据，
     * 最后读出数据显示出来
     */
    public static void main(String[] args) {
//        test1();
        test2();
    }

    private static void test1() {
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建
            Connection conn = DriverManager.getConnection("jdbc:sqlite:sqlite.db");

            Statement stat = conn.createStatement();

            // creat table
            stat.executeUpdate("create table tbl1(name varchar(20), salary int);");//创建一个表，两列

            // insert
            stat.executeUpdate("insert into tbl1 values('ZhangSan', 8000);");//插入数据
            stat.executeUpdate("insert into tbl1 values('LiSi', 7800);");
            stat.executeUpdate("insert into tbl1 values('WangWu', 5800);");
            stat.executeUpdate("insert into tbl1 values('ZhaoLiu', 9100);");

            // delete
            stat.executeUpdate("delete from tbl1 where name='ZhaoLiu'");

            // query
            ResultSet rs = stat.executeQuery("select * from tbl1;");//查询数据
            while (rs.next()) {//将查询到的数据打印出来
                System.out.print("name = " + rs.getString("name") + " ");//列属性一
                System.out.println("salary = " + rs.getString("salary"));//列属性二
            }
            rs.close();
            conn.close();//结束数据库的连接
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void test2() {
        try {
            //连接SQLite的JDBC
            Class.forName("org.sqlite.JDBC");
            //建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建
            Connection conn = DriverManager.getConnection("jdbc:sqlite:sqlite2.db");

            Statement stat = conn.createStatement();

            // creat table
            stat.executeUpdate("create table tbl1(uuid string, ipfsinfo string);");

            // insert
            String string1 = UUID.randomUUID().toString();
            String string2 = UUID.randomUUID().toString();
            stat.executeUpdate("insert into tbl1 values('" + string1 + "', '" + string1 + "');");
            stat.executeUpdate("insert into tbl1 values('" + string2 + "', '" + string2 + "');");

            // delete
            stat.executeUpdate("delete from tbl1 where uuid='" + string1 + "'");

            // query
            ResultSet rs = stat.executeQuery("select * from tbl1;");//查询数据
            while (rs.next()) {//将查询到的数据打印出来
                System.out.print("uuid = " + rs.getString("uuid") + " ");//列属性一
                System.out.println("ipfsinfo = " + rs.getString("ipfsinfo"));//列属性二
            }
            rs.close();
            conn.close();//结束数据库的连接
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}


